package com.cxx.purchasecharge.dal.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import com.cxx.purchasecharge.core.model.OrderSearchForm;
import com.cxx.purchasecharge.core.model.OrderStatusCode;
import com.cxx.purchasecharge.core.model.OrderTypeCode;
import com.cxx.purchasecharge.core.model.persistence.Order;
import com.cxx.purchasecharge.dal.OrderDaoPlus;
import com.cxx.purchasecharge.dal.common.GenericDaoUtils;
import com.cxx.purchasecharge.dal.exception.PCDalException;

public class OrderDaoImpl implements OrderDaoPlus
{
    private static final Logger logger = Logger.getLogger (OrderDaoImpl.class);

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page <Order> findByFuzzy (OrderTypeCode typeCode, Pageable pageable, String searchKey, long signUser,
                                     boolean admin)
    {
        validateTypeCode (typeCode);
        String pageableLog = "offSet:" + pageable.getOffset () + ",pageNumber:" + pageable.getPageNumber ()
                             + ",pageSize:" + pageable.getPageSize () + ",sort:" + pageable.getSort ();
        logger.debug ("Find paging order by fuzzy, pageable: (" + pageableLog + "), searchkey: " + searchKey);
        Page <Order> orderPage;
        String keyWord = (null == searchKey ? "" : searchKey.trim ());
        if (!admin && 0 != signUser)
        {
            String selectCountSql = "SELECT count(o) ";
            String fromSql = "FROM Order o where ";
            String conditionSql = "o.userSigned = ?1 and o.typeCode = ?2 ";
            if (StringUtils.isNotBlank (searchKey))
            {
                conditionSql += "and o.id IN ";
                conditionSql += "(select o1.id from Order o1 where " + "o1.customer.shortName like ?3 or "
                                + "o1.userSigned like ?4 or " + "o1.consigneeName like ?5 or "
                                + "o1.consigneePhone like ?6 or " + "o1.consigneeAddr like ?7 or "
                                + "o1.logisticsNumber like ?8 or " + "o1.networkOrderId like ?9)";
            }
            Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, signUser);
            countQuery.setParameter (2, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                countQuery.setParameter (3, keyWord + "%");
                countQuery.setParameter (4, keyWord + "%");
                countQuery.setParameter (5, keyWord + "%");
                countQuery.setParameter (6, keyWord + "%");
                countQuery.setParameter (7, keyWord + "%");
                countQuery.setParameter (8, keyWord + "%");
                countQuery.setParameter (9, keyWord + "%");
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT o ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            String sortSqlStr = GenericDaoUtils.parseSort (Order.class, pageable, "o");
            if (!"".endsWith (sortSqlStr))
            {
                querySql += sortSqlStr;
            }
            else
            {
                querySql += "o.customer.shortName asc";
            }
            Query query = entityManager.createQuery (querySql);
            query.setParameter (1, signUser);
            query.setParameter (2, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                query.setParameter (3, keyWord + "%");
                query.setParameter (4, keyWord + "%");
                query.setParameter (5, keyWord + "%");
                query.setParameter (6, keyWord + "%");
                query.setParameter (7, keyWord + "%");
                query.setParameter (8, keyWord + "%");
                query.setParameter (9, keyWord + "%");
            }

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Order> goodsList = query.getResultList ();
            orderPage = new PageImpl <Order> (goodsList, pageable, total);
        }
        else
        {
            String selectCountSql = "SELECT count(o) ";
            String fromSql = "FROM Order o where ";
            String conditionSql = "o.typeCode = ?1 ";
            if (StringUtils.isNotBlank (searchKey))
            {
                conditionSql += "and o.id IN ";
                conditionSql = "(select o1.id from Order o1 where " + "o.customer.shortName like ?2 or "
                               + "o.userSigned like ?3 or " + "o.consigneeName like ?4 or "
                               + "o.consigneePhone like ?5 or " + "o.consigneeAddr like ?6 or "
                               + "o.logisticsNumber like ?7 or " + "o.networkOrderId like ?8)";
            }
            Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                countQuery.setParameter (2, keyWord + "%");
                countQuery.setParameter (3, keyWord + "%");
                countQuery.setParameter (4, keyWord + "%");
                countQuery.setParameter (5, keyWord + "%");
                countQuery.setParameter (6, keyWord + "%");
                countQuery.setParameter (7, keyWord + "%");
                countQuery.setParameter (8, keyWord + "%");
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT o ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            String sortSqlStr = GenericDaoUtils.parseSort (Order.class, pageable, "o");
            if (!"".endsWith (sortSqlStr))
            {
                querySql += sortSqlStr;
            }
            else
            {
                querySql += "o.customer.shortName asc";
            }
            Query query = entityManager.createQuery (querySql);
            query.setParameter (1, typeCode);
            if (StringUtils.isNotBlank (searchKey))
            {
                query.setParameter (2, keyWord + "%");
                query.setParameter (3, keyWord + "%");
                query.setParameter (4, keyWord + "%");
                query.setParameter (5, keyWord + "%");
                query.setParameter (6, keyWord + "%");
                query.setParameter (7, keyWord + "%");
                query.setParameter (8, keyWord + "%");
            }

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Order> goodsList = query.getResultList ();
            orderPage = new PageImpl <Order> (goodsList, pageable, total);
        }
        return orderPage;
    }

    @Override
    public Page <Order> findBySearchForm (OrderTypeCode typeCode, Pageable pageable, OrderSearchForm searchForm,
                                          long signUser, boolean admin)
    {
        String pageableLog = "offSet:" + pageable.getOffset () + ",pageNumber:" + pageable.getPageNumber ()
                             + ",pageSize:" + pageable.getPageSize () + ",sort:" + pageable.getSort ();
        logger.debug ("Find paging order by fuzzy, pageable: (" + pageableLog + "), searchForm: " + searchForm);
        validateTypeCode (typeCode);
        Page <Order> orderPage;
        if (!admin && 0 != signUser)
        {
            String selectCountSql = "SELECT count(o) ";
            String fromSql = "FROM Order o where ";
            String conditionSql = "o.userSigned = ?1 and o.typeCode = ?2 ";
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.createDate between ?3 and ?4 and o.customer.id = ?5 ";
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    conditionSql += "and o.createDate between ?3 and ?4 ";
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.customer.id = ?3 ";
                }
            }
            Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, signUser);
            countQuery.setParameter (2, typeCode);
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (3, searchForm.getStartDate ());
                    countQuery.setParameter (4, searchForm.getEndDate ());
                    countQuery.setParameter (5, searchForm.getCustomerId ());
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    countQuery.setParameter (3, searchForm.getStartDate ());
                    countQuery.setParameter (4, searchForm.getEndDate ());
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (3, searchForm.getCustomerId ());
                }
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT o ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            String sortSqlStr = GenericDaoUtils.parseSort (Order.class, pageable, "o");
            if (!"".endsWith (sortSqlStr))
            {
                querySql += sortSqlStr;
            }
            else
            {
                querySql += "o.customer.shortName asc";
            }
            Query query = entityManager.createQuery (querySql);
            query.setParameter (1, signUser);
            query.setParameter (2, typeCode);
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    query.setParameter (3, searchForm.getStartDate ());
                    query.setParameter (4, searchForm.getEndDate ());
                    query.setParameter (5, searchForm.getCustomerId ());
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    query.setParameter (3, searchForm.getStartDate ());
                    query.setParameter (4, searchForm.getEndDate ());
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    query.setParameter (3, searchForm.getCustomerId ());
                }
            }

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Order> goodsList = query.getResultList ();
            orderPage = new PageImpl <Order> (goodsList, pageable, total);
        }
        else
        {
            String selectCountSql = "SELECT count(o) ";
            String fromSql = "FROM Order o where ";
            String conditionSql = "o.typeCode = ?1 ";
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.createDate between ?2 and ?3 and o.customer.id = ?4 ";
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    conditionSql += "and o.createDate between ?2 and ?3 ";
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.customer.id = ?2 ";
                }
            }
            Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, typeCode);
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (2, searchForm.getStartDate ());
                    countQuery.setParameter (3, searchForm.getEndDate ());
                    countQuery.setParameter (4, searchForm.getCustomerId ());
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    countQuery.setParameter (2, searchForm.getStartDate ());
                    countQuery.setParameter (3, searchForm.getEndDate ());
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (2, searchForm.getCustomerId ());
                }
            }
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT o ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            String sortSqlStr = GenericDaoUtils.parseSort (Order.class, pageable, "o");
            if (!"".endsWith (sortSqlStr))
            {
                querySql += sortSqlStr;
            }
            else
            {
                querySql += "o.customer.shortName asc";
            }
            Query query = entityManager.createQuery (querySql);
            query.setParameter (1, typeCode);
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    query.setParameter (2, searchForm.getStartDate ());
                    query.setParameter (3, searchForm.getEndDate ());
                    query.setParameter (4, searchForm.getCustomerId ());
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    query.setParameter (2, searchForm.getStartDate ());
                    query.setParameter (3, searchForm.getEndDate ());
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    query.setParameter (2, searchForm.getCustomerId ());
                }
            }

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Order> goodsList = query.getResultList ();
            orderPage = new PageImpl <Order> (goodsList, pageable, total);
        }
        return orderPage;
    }

    @Override
    public Page <Order> findBySearchForm (boolean out, Pageable pageable, OrderSearchForm searchForm, long signUser,
                                                  boolean admin) throws PCDalException
    {
        String pageableLog = "offSet:" + pageable.getOffset () + ",pageNumber:" + pageable.getPageNumber ()
                + ",pageSize:" + pageable.getPageSize () + ",sort:" + pageable.getSort ();
        logger.debug ("Find paging order by fuzzy, pageable: (" + pageableLog + "), searchForm: " + searchForm);
        Page <Order> orderPage;
        if (!admin && 0 != signUser)
        {
        String selectCountSql = "SELECT count(o) ";
        String fromSql = "FROM Order o where ";
        String conditionSql = "o.userSigned = ?1 and (o.typeCode = ?2 or o.typeCode = ?3) ";
        if (null != searchForm)
        {
           if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
               && 0 != searchForm.getCustomerId ())
           {
               conditionSql += "and o.createDate between ?4 and ?5 and o.customer.id = ?6 ";
           }
           else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
           {
               conditionSql += "and o.createDate between ?4 and ?5 ";
           }
           else if (0 != searchForm.getCustomerId ())
           {
               conditionSql += "and o.customer.id = ?4 ";
           }
        }
        Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
        countQuery.setParameter (1, signUser);
        if(out) 
        {
            countQuery.setParameter (2, OrderTypeCode.OUT);
            countQuery.setParameter (3, OrderTypeCode.OUT_RETURN);
        }
        else 
        {
            countQuery.setParameter (2, OrderTypeCode.IN);
            countQuery.setParameter (3, OrderTypeCode.IN_RETURN);
        }
        if (null != searchForm)
        {
           if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
               && 0 != searchForm.getCustomerId ())
           {
               countQuery.setParameter (4, searchForm.getStartDate ());
               countQuery.setParameter (5, searchForm.getEndDate ());
               countQuery.setParameter (6, searchForm.getCustomerId ());
           }
           else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
           {
               countQuery.setParameter (4, searchForm.getStartDate ());
               countQuery.setParameter (5, searchForm.getEndDate ());
           }
           else if (0 != searchForm.getCustomerId ())
           {
               countQuery.setParameter (4, searchForm.getCustomerId ());
           }
        }
        int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());
        
        String querySql = "SELECT o ";
        querySql += fromSql;
        querySql += conditionSql;
        String orderBySql = " ORDER BY ";
        querySql += orderBySql;
        String sortSqlStr = GenericDaoUtils.parseSort (Order.class, pageable, "o");
        if (!"".endsWith (sortSqlStr))
        {
           querySql += sortSqlStr;
        }
        else
        {
           querySql += "o.customer.shortName asc";
        }
        Query query = entityManager.createQuery (querySql);
        query.setParameter (1, signUser);
        if(out) 
        {
            query.setParameter (2, OrderTypeCode.OUT);
            query.setParameter (3, OrderTypeCode.OUT_RETURN);
        }
        else 
        {
            query.setParameter (2, OrderTypeCode.IN);
            query.setParameter (3, OrderTypeCode.IN_RETURN);
        }
        if (null != searchForm)
        {
           if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
               && 0 != searchForm.getCustomerId ())
           {
               query.setParameter (4, searchForm.getStartDate ());
               query.setParameter (5, searchForm.getEndDate ());
               query.setParameter (6, searchForm.getCustomerId ());
           }
           else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
           {
               query.setParameter (4, searchForm.getStartDate ());
               query.setParameter (5, searchForm.getEndDate ());
           }
           else if (0 != searchForm.getCustomerId ())
           {
               query.setParameter (4, searchForm.getCustomerId ());
           }
        }
        
        query.setFirstResult (pageable.getOffset ());
        query.setMaxResults (pageable.getPageSize ());
        
        @SuppressWarnings ("unchecked")
        List <Order> goodsList = query.getResultList ();
        orderPage = new PageImpl <Order> (goodsList, pageable, total);
        }
        else
        {
        String selectCountSql = "SELECT count(o) ";
        String fromSql = "FROM Order o where ";
        String conditionSql = "(o.typeCode = ?1 or o.typeCode = ?2) ";
        if (null != searchForm)
        {
           if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
               && 0 != searchForm.getCustomerId ())
           {
               conditionSql += "and o.createDate between ?3 and ?4 and o.customer.id = ?5 ";
           }
           else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
           {
               conditionSql += "and o.createDate between ?3 and ?4 ";
           }
           else if (0 != searchForm.getCustomerId ())
           {
               conditionSql += "and o.customer.id = ?3 ";
           }
        }
        Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
        if(out) 
        {
            countQuery.setParameter (1, OrderTypeCode.OUT);
            countQuery.setParameter (2, OrderTypeCode.OUT_RETURN);
        }
        else 
        {
            countQuery.setParameter (1, OrderTypeCode.IN);
            countQuery.setParameter (2, OrderTypeCode.IN_RETURN);
        }
        if (null != searchForm)
        {
           if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
               && 0 != searchForm.getCustomerId ())
           {
               countQuery.setParameter (3, searchForm.getStartDate ());
               countQuery.setParameter (4, searchForm.getEndDate ());
               countQuery.setParameter (5, searchForm.getCustomerId ());
           }
           else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
           {
               countQuery.setParameter (3, searchForm.getStartDate ());
               countQuery.setParameter (4, searchForm.getEndDate ());
           }
           else if (0 != searchForm.getCustomerId ())
           {
               countQuery.setParameter (3, searchForm.getCustomerId ());
           }
        }
        int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());
        
        String querySql = "SELECT o ";
        querySql += fromSql;
        querySql += conditionSql;
        String orderBySql = " ORDER BY ";
        querySql += orderBySql;
        String sortSqlStr = GenericDaoUtils.parseSort (Order.class, pageable, "o");
        if (!"".endsWith (sortSqlStr))
        {
           querySql += sortSqlStr;
        }
        else
        {
           querySql += "o.customer.shortName asc";
        }
        Query query = entityManager.createQuery (querySql);
        if(out) 
        {
            query.setParameter (1, OrderTypeCode.OUT);
            query.setParameter (2, OrderTypeCode.OUT_RETURN);
        }
        else 
        {
            query.setParameter (1, OrderTypeCode.IN);
            query.setParameter (2, OrderTypeCode.IN_RETURN);
        }
        if (null != searchForm)
        {
           if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
               && 0 != searchForm.getCustomerId ())
           {
               query.setParameter (3, searchForm.getStartDate ());
               query.setParameter (4, searchForm.getEndDate ());
               query.setParameter (5, searchForm.getCustomerId ());
           }
           else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
           {
               query.setParameter (3, searchForm.getStartDate ());
               query.setParameter (4, searchForm.getEndDate ());
           }
           else if (0 != searchForm.getCustomerId ())
           {
               query.setParameter (3, searchForm.getCustomerId ());
           }
        }
        
        query.setFirstResult (pageable.getOffset ());
        query.setMaxResults (pageable.getPageSize ());
        
        @SuppressWarnings ("unchecked")
        List <Order> goodsList = query.getResultList ();
        orderPage = new PageImpl <Order> (goodsList, pageable, total);
        }
        return orderPage;
    }

    @Override
    public float countReceivableBy (OrderTypeCode typeCode, OrderSearchForm searchForm,
        long signUser, boolean admin) throws PCDalException
    {
        float sumReceivable = 0;
        if (!admin && 0 != signUser)
        {
            String selectCountSql = "SELECT function ";
            String fromSql = "FROM Order o where ";
            String conditionSql = "o.userSigned = ?1 and o.typeCode = ?2 and o.statusCode = ?3 ";
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.createDate between ?4 and ?5 and o.customer.id = ?6 ";
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    conditionSql += "and o.createDate between ?4 and ?5 ";
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.customer.id = ?4 ";
                }
            }

            String countSql = selectCountSql + fromSql + conditionSql;
            String countReceivable = countSql.replaceFirst ("function", "sum(o.receivable)");
            Query countQuery = entityManager.createQuery (countReceivable);
            countQuery.setParameter (1, signUser);
            countQuery.setParameter (2, typeCode);
            countQuery.setParameter (3, OrderStatusCode.FINISHED);
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (4, searchForm.getStartDate ());
                    countQuery.setParameter (5, searchForm.getEndDate ());
                    countQuery.setParameter (6, searchForm.getCustomerId ());
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    countQuery.setParameter (4, searchForm.getStartDate ());
                    countQuery.setParameter (5, searchForm.getEndDate ());
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (4, searchForm.getCustomerId ());
                }
            }
            sumReceivable = Float.parseFloat (countQuery.getResultList ().get (0).toString ());
        }
        else
        {
            String selectCountSql = "SELECT function ";
            String fromSql = "FROM Order o where ";
            String conditionSql = "o.typeCode = ?1 and o.statusCode = ?2 ";
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.createDate between ?3 and ?4 and o.customer.id = ?5 ";
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    conditionSql += "and o.createDate between ?3 and ?4 ";
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.customer.id = ?3 ";
                }
            }

            String countSql = selectCountSql + fromSql + conditionSql;
            String countReceivable = countSql.replaceFirst ("function", "sum(o.receivable)");
            Query countQuery = entityManager.createQuery (countReceivable);
            countQuery.setParameter (1, typeCode);
            countQuery.setParameter (2, OrderStatusCode.FINISHED);
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (3, searchForm.getStartDate ());
                    countQuery.setParameter (4, searchForm.getEndDate ());
                    countQuery.setParameter (5, searchForm.getCustomerId ());
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    countQuery.setParameter (3, searchForm.getStartDate ());
                    countQuery.setParameter (4, searchForm.getEndDate ());
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (3, searchForm.getCustomerId ());
                }
            }
            sumReceivable = Float.parseFloat (countQuery.getResultList ().get (0).toString ());
        }
        return sumReceivable;
    }

    @Override
    public float countProfitBy (OrderTypeCode typeCode, OrderSearchForm searchForm, long signUser,
                                boolean admin) throws PCDalException
    {
        float sumProfit = 0;
        if (!admin && 0 != signUser)
        {
            String selectCountSql = "SELECT function ";
            String fromSql = "FROM Order o where ";
            String conditionSql = "o.userSigned = ?1 and o.typeCode = ?2 and o.statusCode = ?3 ";
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.createDate between ?4 and ?5 and o.customer.id = ?6 ";
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    conditionSql += "and o.createDate between ?4 and ?5 ";
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.customer.id = ?4 ";
                }
            }

            String countSql = selectCountSql + fromSql + conditionSql;
            String countProfit = countSql.replaceFirst ("function", "sum(o.profit)");
            Query countQuery = entityManager.createQuery (countProfit);
            countQuery.setParameter (1, signUser);
            countQuery.setParameter (2, typeCode);
            countQuery.setParameter (3, OrderStatusCode.FINISHED);
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (4, searchForm.getStartDate ());
                    countQuery.setParameter (5, searchForm.getEndDate ());
                    countQuery.setParameter (6, searchForm.getCustomerId ());
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    countQuery.setParameter (4, searchForm.getStartDate ());
                    countQuery.setParameter (5, searchForm.getEndDate ());
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (4, searchForm.getCustomerId ());
                }
            }
            sumProfit = Float.parseFloat (countQuery.getResultList ().get (0).toString ());
        }
        else
        {
            String selectCountSql = "SELECT function ";
            String fromSql = "FROM Order o where ";
            String conditionSql = "o.typeCode = ?1 and o.statusCode = ?2 ";
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.createDate between ?3 and ?4 and o.customer.id = ?5 ";
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    conditionSql += "and o.createDate between ?3 and ?4 ";
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    conditionSql += "and o.customer.id = ?3 ";
                }
            }

            String countSql = selectCountSql + fromSql + conditionSql;
            String countProfit = countSql.replaceFirst ("function", "sum(o.profit)");
            Query countQuery = entityManager.createQuery (countProfit);
            countQuery.setParameter (1, typeCode);
            countQuery.setParameter (2, OrderStatusCode.FINISHED);
            if (null != searchForm)
            {
                if (null != searchForm.getStartDate () && null != searchForm.getEndDate ()
                    && 0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (3, searchForm.getStartDate ());
                    countQuery.setParameter (4, searchForm.getEndDate ());
                    countQuery.setParameter (5, searchForm.getCustomerId ());
                }
                else if (null != searchForm.getStartDate () && null != searchForm.getEndDate ())
                {
                    countQuery.setParameter (3, searchForm.getStartDate ());
                    countQuery.setParameter (4, searchForm.getEndDate ());
                }
                else if (0 != searchForm.getCustomerId ())
                {
                    countQuery.setParameter (3, searchForm.getCustomerId ());
                }
            }
            sumProfit = Float.parseFloat (countQuery.getResultList ().get (0).toString ());
        }
        return sumProfit;
    }

    private void validateTypeCode (OrderTypeCode typeCode)
    {
        if (null == typeCode)
        {
            throw new PCDalException ("Order type code can not null.");
        }
    }
}
